package br.com.datasun.biblioteca.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.com.datasun.biblioteca.dao.factory.ConnectionFactory;
import br.com.datasun.biblioteca.entities.Contato;
import br.com.datasun.biblioteca.entities.Documento;
import br.com.datasun.biblioteca.entities.Pessoa;
import br.com.datasun.biblioteca.entities.TipoDocumento;
import br.com.datasun.biblioteca.exceptions.DAOException;

public class DocumentoDAO {
	/**
	 * Lista todos os documentos de uma pessoa
	 * @return
	 * @throws DAOException
	 */
	
	public Documento inserir(Documento documento) throws DAOException{
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet = null;
		StringBuilder sql = new StringBuilder();
		sql.append(" insert into documento (numero, id_tipo_documento, id_pessoa) VALUES(?,?,?)");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
								
			preparedStatement.setString(1, documento.getNumero());
			preparedStatement.setLong(2, documento.getTipoDocumento().getId());
			Pessoa pessoa = documento.getPessoa();
			preparedStatement.setLong(3, pessoa.getId());
			preparedStatement.execute();
			resultSet = preparedStatement.getGeneratedKeys();

			if(resultSet.next()){  
			  Long idGerado = resultSet.getLong(1);
			  documento.setId(idGerado);
			}
			
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return documento;
	}
	
	public boolean atualizarDocumento(Documento documento) throws DAOException{
		boolean result = false;
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("update documento ");
		sql.append("   set numero = ? ");
		sql.append("      ,id_tipo_documento = ? " );
		sql.append(" where id = ?");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setString(1, documento.getNumero());
			preparedStatement.setLong(2, documento.getTipoDocumento().getId());
		    preparedStatement.setLong(3, documento.getId());
			preparedStatement.executeUpdate();
			result = true;
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return result;
	}
	
	public boolean excluirDocumentoPorId(Long idDcoumento) throws DAOException{
		boolean result = false;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		StringBuilder sql = new StringBuilder();
		sql.append("delete from documento ");
		sql.append(" where id= ?");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setLong(1, idDcoumento);
			preparedStatement.execute();
			result = true;
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return result;
	}
	
	
	public List<Documento> listar(Pessoa pessoa) throws DAOException{
		List<Documento> resultado = new ArrayList<Documento>();
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("select id, numero, id_tipo_documento from documento");
		sql.append(" where documento.id_pessoa = ?");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setLong(1, pessoa.getId());
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Long id = resultSet.getLong("id");
				String numero = resultSet.getString("numero");
				Long idTipoDocumento = resultSet.getLong("id_tipo_documento");
				TipoDocumento tipoDocumento = TipoDocumento.getValueOf(idTipoDocumento);
				
				Documento documento = new Documento();
				documento.setId(id);
				documento.setNumero(numero);
				documento.setTipoDocumento(tipoDocumento);

				resultado.add(documento);
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return resultado;
		
	}
	
	public boolean existeDocumentoComNumeroTipoDocumento(Documento documento) throws DAOException{
		boolean resultado = false;
		
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("  SELECT 1 existe ");
		sql.append("    FROM documento ");
		sql.append("   WHERE documento.id <> ? ");
		sql.append("     AND documento.tipo_documento = ?");
		sql.append("     AND documento.numero = ?");
		
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setLong(1,documento.getId());
			preparedStatement.setLong(2, documento.getTipoDocumento().getId());
			preparedStatement.setString(3, documento.getNumero());
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()){
				resultado = true;
			}
			
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		
		return resultado;
	}
}
